package cn.itcast.dao.impl;

import cn.itcast.dao.StudentDao;
import cn.itcast.domain.Student;
import cn.itcast.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;

//编译对象更改增删改查
public class StudentDaoImpl02 implements StudentDao {

    @Override
    //查看学生信息
    public ArrayList<Student> findAll() {
        // 定义集合,储存学生数据
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //建立连接
            con = JdbcUtils.getConnection();
            //返回编译对象
            ps = con.prepareStatement("SELECT * FROM student WHERE sid>?");
            //给问号赋值
            ps.setInt(1,0);
            //执行sql语句
            rs = ps.executeQuery();
            //展示
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                String birthday = rs.getString("birthday");
                Student stu = new Student(sid, name, age, birthday);
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtils.close(con,ps,rs);
        }
        return list;
    }

    @Override
    //查找学生数据
    public Student findById(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;
        try {
            //建立连接
            con = JdbcUtils.getConnection();
            //创建编译对象
            ps = con.prepareStatement("SELECT * FROM student WHERE sid=?");
            //给问号赋值
            ps.setInt(1,id);
            //执行sql语句
            rs = ps.executeQuery();
            //查看是否有数据
            if (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                String birthday = rs.getString("birthday");
                student = new Student(sid, name, age, birthday);
            }
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            //释放资源
            JdbcUtils.close(con,ps,rs);
        }
    }

    @Override
    //添加学生数据
    public int insert(Student stu) {
        Connection con = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
            //获取连接
            con = JdbcUtils.getConnection();
            //创建编码对象
            ps = con.prepareStatement("INSERT INTO student(name,age,birthday) VALUES (?,?,?)");
//            Date birthday1 = stu.getBirthday();
//            String birthday = new SimpleDateFormat("yyyy-MM-dd").format(birthday1);
            //给问号赋值
            ps.setObject(1,stu.getName());
            ps.setObject(2,stu.getAge());
            ps.setObject(3,stu.getBirthday());
            //运行sql语句
            result= ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            //释放资源
            JdbcUtils.close(con,ps);
        }
    }

    @Override
    //修改学生数据
    public int update(Student stu) {
        Connection con = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
            //获取连接
            con = JdbcUtils.getConnection();
            //获取编码对象
            ps = con.prepareStatement("UPDATE student SET sid=?,name=?,age=?,birthday=? WHERE sid=?");
            //赋值
            ps.setInt(1,stu.getSid());
            ps.setString(2,stu.getName());
            ps.setInt(3,stu.getAge());
            ps.setString(4,stu.getBirthday());
            ps.setInt(5,stu.getSid());
            //执行sql语句
            result = ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            //释放资源
            JdbcUtils.close(con,ps);
        }
    }

    @Override
    //删除学生数据
    public int delete(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
            //获取连接
            con = JdbcUtils.getConnection();
            //获取编译对象
            ps = con.prepareStatement("DELETE FROM student WHERE sid=?");
            //赋值
            ps.setInt(1,id);
            //执行sql语句
            result = ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            //释放资源
            JdbcUtils.close(con,ps);
        }
    }
}
